package com.xinchuang.service.targetSystem.Impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.UUID;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.xinchuang.comment.R;
import com.xinchuang.common.KeyWordSystem;
import com.xinchuang.common.Result;
import com.xinchuang.common.ResultEnum;
import com.xinchuang.common.ResultUtil;
import com.xinchuang.entity.target.TargetCommonAttribute;
import com.xinchuang.entity.target.TargetSpecialAttribute;
import com.xinchuang.entity.targetSystem.object.TargetAdd;
import com.xinchuang.entity.targetSystem.object.TargetSubAdd;
import com.xinchuang.entity.targetSystem.system.*;
import com.xinchuang.entity.targetSystem.target.TargetMsgTable;
import com.xinchuang.entity.targetSystem.target.TargetSubMsgTable;
import com.xinchuang.entity.targetSystem.target.TargetSubRelationTable;
import com.xinchuang.entity.targetSystem.target.TargetSystemRelation;
import com.xinchuang.mapper.targetSystem.TargetMapper;
import com.xinchuang.mapper.targetSystem.TargetTypeMapper;
import com.xinchuang.pojoUtil.TargetUtil;
import com.xinchuang.service.RedisUseService;
import com.xinchuang.service.target.TargetCommonAttributeService;
import com.xinchuang.service.target.TargetSpecialAttributeService;
import com.xinchuang.service.targetSystem.AsyncService;
import com.xinchuang.service.targetSystem.OfficeUtilService;
import com.xinchuang.service.targetSystem.TargetService;
import com.xinchuang.service.targetSystem.TargetSubTypeService;
import com.xinchuang.service.targetSystem.target.TargetMsgTableService;
import com.xinchuang.service.targetSystem.target.TargetSubMsgTableService;
import com.xinchuang.service.targetSystem.target.TargetSubRelationTableService;
import com.xinchuang.service.targetSystem.target.TargetSystemRelationService;
import com.xinchuang.sqlite.entity.TargetData;
import com.xinchuang.util.FileElseUtil;
import com.xinchuang.util.LngLatUtil;
import com.xinchuang.util.SnowflakeIdWorker;
import com.xinchuang.utils.MyUtil;
import io.lettuce.core.ScriptOutputType;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * TODO
 *
 * @author zkwczx
 * @version 1.0
 * @date 2024/3/20 9:14
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class OfficeUtilServiceImpl implements OfficeUtilService {

    private final TargetTypeMapper targetTypeMapper;

    private final TargetSubTypeService targetSubTypeService;

    private final TargetCommonAttributeService targetCommonAttributeService;

    private final TargetSpecialAttributeService targetSpecialAttributeService;

    private final KeyWordSystem keyWordSystem;

    private final TargetMapper targetMapper;

    private final TargetService targetService;

    private final TargetMsgTableService targetMsgTableService;

    private final TargetSubRelationTableService targetSubRelationTableService;

    private final TargetSubMsgTableService targetSubMsgTableService;

    private final TargetSystemRelationService targetSystemRelationService;

    private final RedisUseService redisUseService;

    private final AsyncService asyncService;

    private SnowflakeIdWorker snow = new SnowflakeIdWorker(1, 1);

    @Override
    public R getTargetExcelModel(JSONObject jsonObject) {
        JSONArray typeList = jsonObject.getJSONArray("typeList");
        if (CollectionUtil.isEmpty(typeList)) {
            return R.fail("请选择类型后重试");
        }
        String typeId = typeList.getString(typeList.size() - 1);
        TargetType targetType = targetTypeMapper.selectTypeOnly(typeId);
        if (ObjectUtil.isNotNull(targetType)) {
            String url = "/file/" + UUID.randomUUID().toString() + "/目标模板.xlsx";
            String fileUrl = keyWordSystem.getFILE_URL() + url;
            FileUtil.mkParentDirs(fileUrl);
            // 创建一个新的工作簿
            Workbook workbook = new XSSFWorkbook();

            // 创建一个工作表
            Sheet sheet = workbook.createSheet("模板");

            //设置宽度
            sheet.setColumnWidth(1, 20 * 256);
            sheet.setColumnWidth(2, 20 * 256);
            sheet.setColumnWidth(3, 20 * 256);
            sheet.setColumnWidth(4, 20 * 256);
            sheet.setColumnWidth(5, 20 * 256);

            List<TargetCommonAttribute> list = targetCommonAttributeService.lambdaQuery()
                    .eq(TargetCommonAttribute::getSign, 1)
                    .orderByAsc(TargetCommonAttribute::getSort)
                    .list();

            Map<String, Integer> rowNum = new HashMap<>();
            rowNum.put("rowNum", 0);
            //创建目标
            targetHeader(sheet, rowNum, list, targetType);

            List<TargetSubType> subTypes = targetSubTypeService.lambdaQuery().eq(TargetSubType::getParentId, typeId).list();

            //创建子目标
            sub(subTypes, sheet, rowNum);

            // 获取或创建列的样式
            CellStyle wrapAndCenterStyle = createWrappedAndCenteredCellStyle(workbook);

            // 假设我们想要设置A列（列索引为0）
            int columnIndexToFormat = 6;
            int maxRows = rowNum.get("rowNum") - 1;

            // 遍历列中的所有单元格并应用样式
            for (int rowIndex = 0; rowIndex <= maxRows; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                for (int i = 0; i < columnIndexToFormat; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        cell = row.createCell(i);
                    }
                    cell.setCellStyle(wrapAndCenterStyle);
                }
            }

            // 写入到文件
            try (FileOutputStream outputStream = new FileOutputStream(fileUrl)) {
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                // 清理资源
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            Map<String, Object> resultMap = new HashMap<>();
            resultMap.put("fileUrl", keyWordSystem.getSERVER_URL() + url);
            return R.data(resultMap);
        }
        return R.fail("类型不存在/已删除");
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public R getModelToTarget(MultipartFile sourceFile) {
        if (ObjectUtil.isNull(sourceFile)) {
            return R.fail("请选择文件上传");
        }
        String iconUrl = null;
        {
            String fileName = sourceFile.getOriginalFilename();
            // 文件存储根路径
            String rootUrl = keyWordSystem.getFILE_URL();
            File rootPath = FileUtil.newFile(rootUrl);
            if (!rootPath.exists()) {
                FileUtil.mkdir(rootPath);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date());
            String middle = "/file/" + date + "/";
            iconUrl = middle + fileName;
            try {
                FileUtil.touch(rootUrl + iconUrl);
                FileUtil.writeBytes(sourceFile.getBytes(), rootUrl + iconUrl);
            } catch (IOException e) {
                FileUtil.del(rootUrl + iconUrl);
                return R.fail("文件读取失败，请检查后重新上传");
            }
        }
        File file = FileUtil.newFile(keyWordSystem.getFILE_URL() + iconUrl);
        if (file.isFile()) {
            try (FileInputStream fis = new FileInputStream(file);
                 Workbook workbook = new XSSFWorkbook(fis)) {

                // 获取第一个工作表
                Sheet sheet = workbook.getSheetAt(0);

                //当前内容列表
                List<TargetData> baseList = new ArrayList<>();
                List<TargetData> detailList = new ArrayList<>();
                List<TargetData> baseSubList = new ArrayList<>();
                List<TargetData> detailSubList = new ArrayList<>();
                //初始化
                String targetId = null;
                String middle = null;
                String subId = null;
                String middleSub = null;
                boolean juage = true;
                //行数
                int row = 0;

                //从到1行开始遍历
                while (true) {
                    //获取当前行
                    Row cells = sheet.getRow(row);
                    //判断内容为空时结束
                    if (ObjectUtil.isNull(cells) || StringUtils.isBlank(getCellValue(cells.getCell(0)))) {
                        if (CollectionUtil.isNotEmpty(baseList)) {
                            getTargetMsg(middle, baseList, detailList, "file");
                        }
                        if (CollectionUtil.isNotEmpty(baseSubList)) {
                            getSubMsg(middle, middleSub, baseSubList, detailSubList, "file");
                        }
                        break;
                    }
                    //判断第一行是否为MB
                    String cellValue = getCellValue(cells.getCell(0));
                    switch (cellValue) {
                        case "MB":
                            targetId = snow.nextId() + "";
                            if (null == middle) {
                                middle = targetId;
                            }
                            if (!targetId.equals(middle)) {
                                //一个目标写完录入数据
                                getTargetMsg(middle, baseList, detailList, "file");
                                if (CollectionUtil.isNotEmpty(baseSubList)) {
                                    getSubMsg(middle, middleSub, baseSubList, detailSubList, "file");
                                }
                                middle = targetId;
                                baseList = new ArrayList<>();
                                detailList = new ArrayList<>();
                                baseSubList = new ArrayList<>();
                                detailSubList = new ArrayList<>();
                                juage = true;
                            }
                            break;
                        case "SUB":
                            subId = snow.nextId() + "";
                            juage = false;
                            if (null == middleSub) {
                                middleSub = subId;
                            }
                            if (null != targetId && !subId.equals(middleSub)) {
                                getSubMsg(middle, middleSub, baseSubList, detailSubList, "file");
                                middleSub = subId;
                                baseSubList = new ArrayList<>();
                                detailSubList = new ArrayList<>();
                            }
                            break;
                        default:
                            TargetData data = new TargetData();
                            data.setKey(getCellValue(cells.getCell(1)));
                            data.setKeyName(getCellValue(cells.getCell(3)));
                            data.setKeyType(getCellValue(cells.getCell(2)));
                            data.setKeyValue(getCellValue(cells.getCell(4)));
                            if (juage) {
                                if (cellValue.contains("x")) {
                                    detailList.add(data);
                                } else {
                                    baseList.add(data);
                                }
                            } else {
                                if (cellValue.contains("x")) {
                                    detailSubList.add(data);
                                } else {
                                    baseSubList.add(data);
                                }
                            }
                            break;
                    }
                    row++;
                }

                // 遍历行
//                for (Row row : sheet) {
//                    //判断第一行
//                    getCellValue(row.getCell(0));
//                    // 遍历单元格
//                    for (Cell cell : row) {
//
//                        String cellValue = getCellValue(cell);
//                        System.out.print(cellValue + "\t");
//                    }
//                    System.out.println();
//                }
                return R.success("操作成功");
            } catch (IOException e) {
                e.printStackTrace();
                return R.fail("文件去读失败，请重新确认文件格式及内容");
            }
        }
        return R.fail("文件不存在/已删除");
    }

    @Override
    public R exportElseTarget(String typeName, MultipartFile sourceFile) {
        try {
            Workbook workbook = new XSSFWorkbook(sourceFile.getInputStream());
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            int row = 0;
            TargetType selectOne = targetTypeMapper.selectOne(new LambdaQueryWrapper<TargetType>()
                    .eq(TargetType::getParentId, 1).eq(TargetType::getTypeName, typeName)
                    .last("LIMIT 1"));
            if (ObjectUtil.isNull(selectOne)) {
                selectOne = new TargetType();
                selectOne.setTypeId(snow.nextId() + "");
                selectOne.setParentId("1");
                selectOne.setTypeName(typeName);
                selectOne.setLevel("1");
                Integer count = targetTypeMapper.selectSpecialCount("1");
                if (null == count) {
                    selectOne.setSort(1);
                } else {
                    selectOne.setSort(count + 1);
                }
                //保存类型
                targetTypeMapper.insert(selectOne);
                targetTypeMapper.addSign(selectOne.getTypeId(), "default.png");
                redisUseService.updateTypeName(selectOne.getTypeId(), selectOne.getTypeName());
            }
            List<String> header = new ArrayList<>();
            while (true) {
                Row cells = sheet.getRow(row);
                if (null == cells) {
                    break;
                }
                if (0 == row) {
                    short cellNum = cells.getLastCellNum();
                    for (int i = 2; i < cellNum - 2; i++) {
                        header.add(cells.getCell(i).getStringCellValue());
                    }
                } else {
                    JSONArray arrayType = new JSONArray();
                    arrayType.add(selectOne.getTypeId());
                    //构建目标基础数据
                    com.xinchuang.entity.targetSystem.target.TargetData targetData = getTargetData(cells.getCell(1).getStringCellValue(), arrayType.toJSONString(), selectOne.getTypeId());
                    //保存基础
                    targetMapper.saveTime(targetData);
                    //目标基础数据
                    List<TargetCommonAttribute> commonAttributes = targetCommonAttributeService.lambdaQuery().eq(TargetCommonAttribute::getSign, 1).list();
                    List<TargetMsgTable> msgTables = getTargetBase(targetData, "1", commonAttributes);
                    targetMsgTableService.saveBatch(msgTables);
                    redisUseService.updateTargetBase(targetData.getTargetId(), msgTables);
                    //详细数据
                    List<TargetSpecialAttribute> attributes = targetSpecialAttributeService.lambdaQuery().eq(TargetSpecialAttribute::getTypeId, selectOne.getTypeId()).list();
                    getSpecial(attributes, cells, header, targetData.getTargetId(), selectOne.getTypeId());
                }
                row++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return R.success("导入成功");
    }

    public Result exportEmulation(MultipartFile sourceFile) {
        List<Object> resultList = new ArrayList<>();
        try {
            Workbook workbook = new XSSFWorkbook(sourceFile.getInputStream());
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            int row = 1;
            while (true) {
                Row sheetRow = sheet.getRow(row);
                if (null == sheetRow) {
                    break;
                }
                String lon = getCellValue(sheetRow.getCell(keyWordSystem.getExportLon() - 1));
                String lat = getCellValue(sheetRow.getCell(keyWordSystem.getExportLat() - 1));
                String height = getCellValue(sheetRow.getCell(keyWordSystem.getExportHeight() - 1));
                List<Double> list = new ArrayList<>();
                list.add(FileElseUtil.getStringTransitionDouble(lon));
                list.add(FileElseUtil.getStringTransitionDouble(lat));
                list.add(FileElseUtil.getStringTransitionDouble(height));
                resultList.add(list);
                row++;
            }
        } catch (Exception e) {
            log.error("数据导入异常:" + e.getMessage(), e);
            return ResultUtil.error(500, "数据导入异常，请检查文件后重试");
        }
        return ResultUtil.success(resultList);
    }

    private com.xinchuang.entity.targetSystem.target.TargetData getTargetData(String name, String type, String typeId) {
        com.xinchuang.entity.targetSystem.target.TargetData targetData = new com.xinchuang.entity.targetSystem.target.TargetData();
        targetData.setTargetId(snow.nextId() + "");
        targetData.setTargetClassify(type);
        targetData.setType(typeId);
        targetData.setTargetName(name);
        targetData.setGrade("");
        targetData.setCountry("");
        targetData.setLon(0.09887316937991386);
        targetData.setLat(0.08726753962206146);
        targetData.setHeight(0D);
        targetData.setIndexValue("0");
        targetData.setRisk("0");
        targetData.setDeleteSign(1);
        return targetData;
    }

    private List<TargetMsgTable> getTargetBase(com.xinchuang.entity.targetSystem.target.TargetData targetData, String userId, List<TargetCommonAttribute> common) {
        List<TargetMsgTable> msgList = new ArrayList<>();
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("newimageUrl");
            targetMsg.setKeyName("图片");
            targetMsg.setKeyType("img");
            targetMsg.setKeyValue("[]");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("plotinfoobj");
            targetMsg.setKeyName("标绘信息");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("area");
            targetMsg.setKeyName("面积");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("0");
            targetMsg.setRemark("㎡");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("targetName");
            targetMsg.setKeyName("名称");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue(targetData.getTargetName());
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("targetClassify");
            targetMsg.setKeyName("类别");
            targetMsg.setKeyType("select");
            targetMsg.setKeyValue(targetData.getTargetClassify());
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("country");
            targetMsg.setKeyName("国家/地区");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue(targetData.getCountry());
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("grade");
            targetMsg.setKeyName("目标等级");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue(targetData.getGrade());
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("position");
            targetMsg.setKeyName("经纬度");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("[" + targetData.getLon() + "," + targetData.getLat() + ",0]");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("height");
            targetMsg.setKeyName("高程");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("");
            targetMsg.setRemark("m");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("riskLevel");
            targetMsg.setKeyName("危险程度");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("0");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("desicribe");
            targetMsg.setKeyName("描述");
            targetMsg.setKeyType("String");
            targetMsg.setKeyValue("");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        for (TargetCommonAttribute commonAttribute : common) {
            if ("desicribe".equals(commonAttribute.getKeyValue())) {
                continue;
            }
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetData.getTargetId());
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId(commonAttribute.getKeyValue());
            targetMsg.setKeyName(commonAttribute.getKeyName());
            targetMsg.setKeyType(commonAttribute.getKeyType());
            targetMsg.setKeyValue("");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        return msgList;
    }

    private void getSpecial(List<TargetSpecialAttribute> specialAttributes, Row cells, List<String> header, String targetId, String typeId) {
        LinkedHashMap<String, String> map = new LinkedHashMap<>();
        Set<String> set = new HashSet<>();

        for (int i = 0; i < header.size(); i++) {
            map.put(header.get(i), getCellValue(cells.getCell(i + 2)));
        }

        for (TargetSpecialAttribute specialAttribute : specialAttributes) {
            set.add(specialAttribute.getKeyName());
        }

        int sign = specialAttributes.size();
        for (String s : map.keySet()) {
            if (!set.contains(s)) {
                TargetSpecialAttribute specialAttribute = new TargetSpecialAttribute();
                specialAttribute.setTypeId(typeId);
                specialAttribute.setKeyName(s);
                specialAttribute.setKeyValue(getRandomName(set));
                specialAttribute.setKeyType("String");
                specialAttribute.setShowType("text");
                specialAttribute.setRequired("false");
                specialAttribute.setInfo("false");
                specialAttribute.setVerify("false");
                specialAttribute.setEnumerator("[]");
                specialAttribute.setSort(sign);
                specialAttributes.add(specialAttribute);
                sign++;
            }
        }
        targetSpecialAttributeService.saveOrUpdateBatch(specialAttributes);
        List<TargetMsgTable> targetMsgTables = new ArrayList<>();
        for (TargetSpecialAttribute attribute : specialAttributes) {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetId);
            targetMsg.setMsgType(2);
            targetMsg.setPerson("1");
            targetMsg.setKeyId(attribute.getKeyValue());
            targetMsg.setKeyName(attribute.getKeyName());
            targetMsg.setKeyType(attribute.getKeyType());
            Object value = map.get(attribute.getKeyName());
            targetMsg.setKeyValue(ObjectUtil.isNotEmpty(value) ? value.toString() : "");
            targetMsg.setRemark("");
            targetMsgTables.add(targetMsg);
        }
        targetMsgTableService.saveBatch(targetMsgTables);
        redisUseService.updateTargetSpecial(targetId, targetMsgTables);
    }

    private String getRandomName(Set<String> set) {
        String str = MyUtil.getRandomStr(8);
        if (set.contains(str)) {
            return getRandomName(set);
        }
        set.add(str);
        return str;
    }

    //读取模板

    /**
     * 保存目标基本数据值
     *
     * @param targetId
     * @param baseList
     * @param detailList
     * @param userId
     */
    private void getTargetMsg(String targetId, List<TargetData> baseList, List<TargetData> detailList, String userId) {
        if (CollectionUtil.isEmpty(baseList)) {
            return;
        }
        com.xinchuang.entity.targetSystem.target.TargetData targetData = new com.xinchuang.entity.targetSystem.target.TargetData();
        //基础参数
        targetData.setTargetId(targetId);
        //详细参数
        List<TargetMsgTable> msgList = new ArrayList<>();
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetId);
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("newimageUrl");
            targetMsg.setKeyName("图片");
            targetMsg.setKeyType("img");
            targetMsg.setKeyValue("[]");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetId);
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("plotinfoobj");
            targetMsg.setKeyName("标绘信息");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("");
            targetMsg.setRemark("");
            msgList.add(targetMsg);
        }
        {
            TargetMsgTable targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetId);
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId("area");
            targetMsg.setKeyName("面积");
            targetMsg.setKeyType("text");
            targetMsg.setKeyValue("");
            targetMsg.setRemark("㎡");
            msgList.add(targetMsg);
        }
        TargetMsgTable targetMsg = null;
        for (com.xinchuang.sqlite.entity.TargetData data : baseList) {
            //单条属性值封装
            targetMsg = new TargetMsgTable();
            targetMsg.setRedId(targetId);
            targetMsg.setMsgType(1);
            targetMsg.setPerson(userId);
            targetMsg.setKeyId(data.getKey());
            targetMsg.setKeyName(data.getKeyName());
            targetMsg.setKeyType(data.getKeyType());
            if (data.getKeyType().equals("Double")) {
                targetMsg.setKeyValue(FileElseUtil.getStringTransitionDouble(data.getKeyValue()) + "");
            } else {
                targetMsg.setKeyValue(data.getKeyValue());
            }
            targetMsg.setRemark("");
            switch (data.getKey()) {
                case "targetName":
                    targetData.setTargetName(data.getKeyValue());
                    break;
                case "targetClassify":
                    targetData.setTargetClassify(data.getKeyValue());
                    targetData.setType(TargetUtil.getParamListOrString(data.getKeyValue()));
                    break;
                case "grade":
                    targetData.setGrade(data.getKeyValue());
                    break;
                case "country":
                    if (data.getKeyValue().contains("[")) {
                        List list = FileElseUtil.getStringTransitionList(data.getKeyValue());
                        if (CollectionUtil.isNotEmpty(list) && list.size() == 1) {
                            targetData.setCountry(list.get(0).toString());
                        } else {
                            targetData.setCountry(data.getKeyValue());
                        }
                    } else {
                        targetData.setCountry(data.getKeyValue());
                    }
                    break;
                case "position":
                    List list = LngLatUtil.formatLngLat(data.getKeyValue());
                    targetData.setLon((double) list.get(0));
                    targetData.setLat((double) list.get(1));
                    targetData.setHeight(Double.valueOf(list.get(2).toString()));
                    targetMsg.setKeyValue("[" + list.get(0) + "," + list.get(1) + ",0]");
                    break;
                case "riskLevel":
                    if (StringUtils.isNumeric(data.getKeyValue())) {
                        targetData.setRisk(data.getKeyValue());
                    } else {
                        targetMsg.setKeyValue("0");
                        targetData.setRisk("0");
                    }
                    break;
                case "height":
                    targetMsg.setRemark("m");
                    break;
                default:
                    break;
            }
            msgList.add(targetMsg);
        }
        targetData.setDeleteSign(1);
        targetData.setIndexValue("0");
        targetData.setDeleteSign(1);
        //保存基础
        targetMapper.save(targetData);
        //属性保存
        targetMsgTableService.saveBatch(msgList);
        //系统类型归类
        targetSystemRelationService.save(
                TargetSystemRelation.builder()
                        .targetId(targetId)
                        .targetClassify(targetData.getType())
                        .country(targetData.getCountry())
                        .build()
        );
        //构建指标值
        asyncService.createIndexInit(targetData.getTargetName(), targetData.getType(), targetData.getTargetId());
        if (CollectionUtil.isNotEmpty(detailList)) {
            //特有数据
            List<TargetMsgTable> detailMsgList = new ArrayList<>();
            for (com.xinchuang.sqlite.entity.TargetData data : detailList) {
                //单条属性值封装
                targetMsg = new TargetMsgTable();
                targetMsg.setRedId(targetId);
                targetMsg.setMsgType(2);
                targetMsg.setPerson(userId);
                targetMsg.setKeyId(data.getKey());
                targetMsg.setKeyName(data.getKeyName());
                targetMsg.setKeyType(data.getKeyType());
                if (data.getKeyType().equals("Double")) {
                    targetMsg.setKeyValue(FileElseUtil.getStringTransitionDouble(data.getKeyValue()) + "");
                } else {
                    targetMsg.setKeyValue(data.getKeyValue());
                }
                targetMsg.setRemark(data.getRemark());
                detailMsgList.add(targetMsg);
            }
            targetMsgTableService.saveOrUpdateBatch(detailMsgList);
        }
    }

    /**
     * 保存子目标基本数据值
     *
     * @param targetId
     * @param baseList
     * @param detailList
     * @param userId
     */
    private void getSubMsg(String targetId, String subId, List<TargetData> baseList, List<TargetData> detailList, String userId) {
        if (CollectionUtil.isEmpty(baseList)) {
            return;
        }
        //目标与子目标关联
        TargetSubRelationTable relationTable = new TargetSubRelationTable();
        relationTable.setTargetId(targetId);
        relationTable.setSubId(subId);
        relationTable.setPreType(1);
        targetSubRelationTableService.save(relationTable);
        //基本参数
        {
            List<TargetSubMsgTable> msgList = new ArrayList<>();
            {
                TargetSubMsgTable targetMsg = new TargetSubMsgTable();
                targetMsg.setSubId(subId);
                targetMsg.setMsgType(1);
                targetMsg.setPerson(userId);
                targetMsg.setKeyId("newimageUrl");
                targetMsg.setKeyName("图片");
                targetMsg.setKeyType("img");
                targetMsg.setKeyValue("[]");
                targetMsg.setRemark("");
                msgList.add(targetMsg);
            }
            {
                TargetSubMsgTable targetMsg = new TargetSubMsgTable();
                targetMsg.setSubId(subId);
                targetMsg.setMsgType(1);
                targetMsg.setPerson(userId);
                targetMsg.setKeyId("plotinfoobj");
                targetMsg.setKeyName("标绘信息");
                targetMsg.setKeyType("text");
                targetMsg.setKeyValue("");
                targetMsg.setRemark("");
                msgList.add(targetMsg);
            }
            {
                TargetSubMsgTable targetMsg = new TargetSubMsgTable();
                targetMsg.setSubId(subId);
                targetMsg.setMsgType(1);
                targetMsg.setPerson(userId);
                targetMsg.setKeyId("area");
                targetMsg.setKeyName("面积");
                targetMsg.setKeyType("text");
                targetMsg.setKeyValue("");
                targetMsg.setRemark("㎡");
                msgList.add(targetMsg);
            }
            TargetSubMsgTable targetSubMsg;
            for (com.xinchuang.sqlite.entity.TargetData data : baseList) {
                //单条属性值封装
                targetSubMsg = new TargetSubMsgTable();
                targetSubMsg.setSubId(subId);
                targetSubMsg.setMsgType(1);
                targetSubMsg.setPerson(userId);
                targetSubMsg.setKeyId(data.getKey());
                targetSubMsg.setKeyName(data.getKeyName());
                targetSubMsg.setKeyType(data.getKeyType());
                if (data.getKeyType().equals("Double")) {
                    targetSubMsg.setKeyValue(FileElseUtil.getStringTransitionDouble(data.getKeyValue()) + "");
                } else {
                    targetSubMsg.setKeyValue(data.getKeyValue());
                }
                targetSubMsg.setRemark("");
                switch (data.getKey()) {
                    case "targetName":
                        if (StringUtils.isBlank(data.getKeyValue())) {
                            targetSubMsg.setKeyValue("子目标");
                        }
                        break;
                    case "riskLevel":
                        if (StringUtils.isNumeric(data.getKeyValue())) {
                            targetSubMsg.setKeyValue("0");
                        } else {
                            targetSubMsg.setKeyValue("0");
                        }
                        break;
                    case "position":
                        List list = LngLatUtil.formatLngLat(data.getKeyValue());
                        targetSubMsg.setKeyValue("[" + list.get(0) + "," + list.get(1) + ",0]");
                        break;
                    case "height":
                        targetSubMsg.setRemark("m");
                        break;
                    default:
                        targetSubMsg.setRemark("");
                        break;
                }
                msgList.add(targetSubMsg);
            }
            targetSubMsgTableService.saveBatch(msgList);
        }
        if (CollectionUtil.isNotEmpty(detailList)) {
            List<TargetSubMsgTable> msgList = new ArrayList<>();
            TargetSubMsgTable targetSubMsg;
            for (com.xinchuang.sqlite.entity.TargetData data : detailList) {
                //单条属性值封装
                targetSubMsg = new TargetSubMsgTable();
                targetSubMsg.setSubId(subId);
                targetSubMsg.setMsgType(2);
                targetSubMsg.setPerson(userId);
                targetSubMsg.setKeyId(data.getKey());
                targetSubMsg.setKeyName(data.getKeyName());
                targetSubMsg.setKeyType(data.getKeyType());
                if (data.getKeyType().equals("Double")) {
                    targetSubMsg.setKeyValue(FileElseUtil.getStringTransitionDouble(data.getKeyValue()) + "");
                } else {
                    targetSubMsg.setKeyValue(data.getKeyValue());
                }
                targetSubMsg.setRemark("");
                msgList.add(targetSubMsg);
            }
            targetSubMsgTableService.saveBatch(msgList);
        }
    }

    /**
     * 读取单元格数据
     *
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            // 其他类型如公式、错误值等，根据需要处理
            default:
                return "";
        }
    }

    //TODO: 写入模板

    public void targetHeader(Sheet sheet, Map<String, Integer> rowNum, List<TargetCommonAttribute> list, TargetType targetType) {
        Integer num = rowNum.get("rowNum");
        // 创建行（0基索引）
        Row row = sheet.createRow(num);
        //头部信息
        Cell cell = row.createCell(0);
        cell.setCellValue("MB");
        cell = row.createCell(1);
        cell.setCellValue("字段名称（不可修改）");
        cell = row.createCell(2);
        cell.setCellValue("字段类型（不可修改）");
        cell = row.createCell(3);
        cell.setCellValue("属性名称（不可修改）");
        cell = row.createCell(4);
        cell.setCellValue("详细参数（请填写）");
        cell = row.createCell(5);
        cell.setCellValue("样例及说明");
        num++;
        rowNum.put("rowNum", num);
        rowNum.put("idx", 1);
        targetFixParam(sheet, rowNum, "[\"" + targetType.getTypeId() + "\"]", targetType.getTypeName());
        getGradePull(sheet, 4, 4);
        getMsg(sheet, rowNum, list);
        List<TargetSpecialAttribute> specialAttributes = targetSpecialAttributeService.lambdaQuery().eq(TargetSpecialAttribute::getTypeId, targetType.getTypeId()).list();
        getTypeMsg(sheet, rowNum, specialAttributes);
    }

    public void targetFixParam(Sheet sheet, Map<String, Integer> rowNum, String typeId, String typeName) {
        Integer num = rowNum.get("rowNum");
        Integer idx = rowNum.get("idx");
        Map<String, String> map = new LinkedHashMap<>();
        map.put("targetName", "名称");
        map.put("targetClassify", "类别");
        map.put("country", "国家/地区");
        map.put("grade", "目标等级");
        map.put("position", "经纬度");
        map.put("height", "高程");
        map.put("riskLevel", "危险程度");
        for (String s : map.keySet()) {
            Row row = sheet.createRow(num);
            Cell cell = row.createCell(0);
            cell.setCellValue(idx);
            cell = row.createCell(1);
            cell.setCellValue(s);
            cell = row.createCell(2);
            switch (s) {
                case "targetClassify":
                    cell.setCellValue("select");
                    break;
                default:
                    cell.setCellValue("text");
                    break;
            }
            cell = row.createCell(3);
            cell.setCellValue(map.get(s));
            cell = row.createCell(4);
            switch (s) {
                case "targetClassify":
                    cell.setCellValue(typeId);
                    break;
                case "position":
                    cell.setCellValue("116°25'7.85\",40°5'23.03\"");
                    break;
                default:
                    cell.setCellValue("");
                    break;
            }
            cell = row.createCell(5);
            switch (s) {
                case "targetClassify":
                    cell.setCellValue("选择类型为：(" + typeName + ")系统定义请勿修改");
                    break;
                case "grade":
                    cell.setCellValue("请从下拉中选择数值");
                    break;
                case "position":
                    cell.setCellValue("请根据样例录入（116°25'7.85\",40°5'23.03\")中间请以英文逗号间隔");
                    break;
                case "height":
                case "riskLevel":
                    cell.setCellValue("请填写'数值'型");
                    break;
                default:
                    cell.setCellValue("");
                    break;
            }
            num++;
            idx++;
        }
        rowNum.put("rowNum", num);
        rowNum.put("idx", idx);
    }

    public void getGradePull(Sheet sheet, int row, int col) {
        // 定义下拉列表的选项
        String[] items = {"一级", "二级", "三级"};

        // 获取XSSFDataValidationHelper对象
        XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);

        // 创建基于显式列表的约束
        XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) validationHelper.createExplicitListConstraint(items);

        // 定义要在其中应用下拉列表的单元格范围，这里仅应用于单个单元格A1
        CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);

        // 根据约束和地址列表创建DataValidation对象
        DataValidation validation = validationHelper.createValidation(constraint, addressList);

        // 将数据验证添加到工作表的单元格A1
        sheet.addValidationData(validation);
    }

    public void getMsg(Sheet sheet, Map<String, Integer> rowNum, List<TargetCommonAttribute> list) {
        if (CollectionUtil.isNotEmpty(list)) {
            Integer num = rowNum.get("rowNum");
            Integer idx = rowNum.get("idx");
            for (TargetCommonAttribute attribute : list) {
                Row row = sheet.createRow(num);
                Cell cell = row.createCell(0);
                cell.setCellValue(idx);
                cell = row.createCell(1);
                cell.setCellValue(attribute.getKeyValue());
                cell = row.createCell(2);
                cell.setCellValue(attribute.getKeyType());
                cell = row.createCell(3);
                cell.setCellValue(attribute.getKeyName());
                cell = row.createCell(4);
                cell.setCellValue("");
                cell = row.createCell(5);
                if (attribute.getKeyType().equals("Double")) {
                    cell.setCellValue("请填写'数值'型");
                } else {
                    cell.setCellValue("");
                }
                num++;
                idx++;
            }
            rowNum.put("rowNum", num);
            rowNum.put("idx", idx);
        }
    }

    public void getTypeMsg(Sheet sheet, Map<String, Integer> rowNum, List<TargetSpecialAttribute> list) {
        if (CollectionUtil.isNotEmpty(list)) {
            Integer num = rowNum.get("rowNum");
            Integer idx = rowNum.get("idx");
            for (TargetSpecialAttribute attribute : list) {
                Row row = sheet.createRow(num);
                Cell cell = row.createCell(0);
                cell.setCellValue("x" + idx);
                cell = row.createCell(1);
                cell.setCellValue(attribute.getKeyValue());
                cell = row.createCell(2);
                cell.setCellValue(attribute.getKeyType());
                cell = row.createCell(3);
                cell.setCellValue(attribute.getKeyName());
                cell = row.createCell(4);
                cell.setCellValue("");
                cell = row.createCell(5);
                if (attribute.getKeyType().equals("Double")) {
                    cell.setCellValue("请填写'数值'型");
                } else {
                    cell.setCellValue("");
                }
                num++;
                idx++;
            }
            rowNum.put("rowNum", num);
            rowNum.put("idx", idx);
        }
    }

    public void sub(List<TargetSubType> list, Sheet sheet, Map<String, Integer> rowNum) {
        if (CollectionUtil.isNotEmpty(list)) {
            Integer num = rowNum.get("rowNum");
            List<TargetCommonAttribute> attributes = targetCommonAttributeService.lambdaQuery()
                    .eq(TargetCommonAttribute::getSign, 2)
                    .orderByAsc(TargetCommonAttribute::getSort)
                    .list();
            for (TargetSubType targetSubType : list) {
                // 创建行（0基索引）
                Row row = sheet.createRow(num);
                //头部信息
                Cell cell = row.createCell(0);
                cell.setCellValue("SUB");
                cell = row.createCell(1);
                cell.setCellValue("字段名称（不可修改）");
                cell = row.createCell(2);
                cell.setCellValue("字段类型（不可修改）");
                cell = row.createCell(3);
                cell.setCellValue("属性名称（不可修改）");
                cell = row.createCell(4);
                cell.setCellValue("详细参数（请填写）");
                cell = row.createCell(5);
                cell.setCellValue("样例及说明");
                num++;
                rowNum.put("rowNum", num);
                rowNum.put("idx", 1);
                subFixParam(sheet, rowNum, targetSubType.getTypeId(), targetSubType.getTypeName());
                getMsg(sheet, rowNum, attributes);
                List<TargetSpecialAttribute> specialAttributes = targetSpecialAttributeService.lambdaQuery().eq(TargetSpecialAttribute::getTypeId, targetSubType.getTypeId()).list();
                getTypeMsg(sheet, rowNum, specialAttributes);
            }
        }
    }

    public void subFixParam(Sheet sheet, Map<String, Integer> rowNum, String typeId, String typeName) {
        Integer num = rowNum.get("rowNum");
        Integer idx = rowNum.get("idx");
        Map<String, String> map = new LinkedHashMap<>();
        map.put("targetName", "名称");
        map.put("targetClassify", "类别");
        map.put("position", "经纬度");
        map.put("height", "高程");
        map.put("riskLevel", "危险程度");
        for (String s : map.keySet()) {
            Row row = sheet.createRow(num);
            Cell cell = row.createCell(0);
            cell.setCellValue(idx);
            cell = row.createCell(1);
            cell.setCellValue(s);
            cell = row.createCell(2);
            switch (s) {
                case "targetClassify":
                    cell.setCellValue("select");
                    break;
                default:
                    cell.setCellValue("text");
                    break;
            }
            cell = row.createCell(3);
            cell.setCellValue(map.get(s));
            cell = row.createCell(4);
            switch (s) {
                case "targetClassify":
                    cell.setCellValue("[\"" + typeId + "\"]");
                    break;
                case "position":
                    cell.setCellValue("116°25'7.85\",40°5'23.03\"");
                    break;
                default:
                    cell.setCellValue("");
                    break;
            }
            cell = row.createCell(5);
            switch (s) {
                case "targetClassify":
                    cell.setCellValue("选择类型为：(" + typeName + ")系统定义请勿修改");
                    break;
                case "position":
                    cell.setCellValue("请根据样例录入（116°25'7.85\",40°5'23.03\")中间请以英文逗号间隔");
                    break;
                case "height":
                case "riskLevel":
                    cell.setCellValue("请填写'数值'型");
                    break;
                default:
                    cell.setCellValue("");
                    break;
            }
            num++;
            idx++;
        }
        rowNum.put("rowNum", num);
        rowNum.put("idx", idx);
    }

    private CellStyle createWrappedAndCenteredCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();

        // 设置自动换行
        cellStyle.setWrapText(true);

        // 设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 设置垂直居中（可选，如果你需要垂直居中的话）
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cellStyle.setBorderTop(BorderStyle.THIN); // 设置上边框为细线
        cellStyle.setBorderBottom(BorderStyle.THIN); // 设置下边框为细线
        cellStyle.setBorderLeft(BorderStyle.THIN); // 设置左边框为细线
        cellStyle.setBorderRight(BorderStyle.THIN); // 设置右边框为细线

        return cellStyle;
    }

}
